/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tces.controller;

import com.tces.model.Instructor;
import com.tces.model.Subject;
import com.tces.utils.ConnectionDAO;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import org.apache.log4j.Logger;

/**
 *
 * @author Arnold
 */
public class InstructorDAO extends ConnectionDAO {

    private static final Logger log = Logger.getLogger(InstructorDAO.class);

    public static List<Instructor> getFullname() throws Exception {
        List<Instructor> nameList = new ArrayList<Instructor>();
        String sql = "SELECT\n"
                + "	i.instructor_id,\n"
                + "	concat_ws(\n"
                + "		' ',\n"
                + "		concat_ws(\n"
                + "			', ',\n"
                + "			i.instructor_last_name,\n"
                + "			i.instructor_first_name\n"
                + "		),\n"
                + "		CONCAT(\n"
                + "			SUBSTR(\n"
                + "				i.instructor_middle_name,\n"
                + "				1,\n"
                + "				1\n"
                + "			),\n"
                + "			'.'\n"
                + "		)\n"
                + "	) instructor_fullname\n"
                + "FROM\n"
                + "	instructors i\n"
                + "ORDER BY concat_ws(\n"
                + "		' ',\n"
                + "		concat_ws(\n"
                + "			', ',\n"
                + "			i.instructor_last_name,\n"
                + "			i.instructor_first_name\n"
                + "		),\n"
                + "		CONCAT(\n"
                + "			SUBSTR(\n"
                + "				i.instructor_middle_name,\n"
                + "				1,\n"
                + "				1\n"
                + "			),\n"
                + "			'.'\n"
                + "		)\n"
                + "	)";

        PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
        log.info(preparedStatement.toString());
        ResultSet rs = preparedStatement.executeQuery();
        while (rs.next()) {
            Instructor instructor = new Instructor();
            instructor.setInstructorID(rs.getInt("instructor_id"));
            instructor.setInstructorFullname(rs.getString("instructor_fullname"));
            nameList.add(instructor);
        }
        closeConnection(getConnectionObject());
        return nameList;
    }
    public static List<Instructor> getFullname(int collegeID) throws Exception {
        List<Instructor> nameList = new ArrayList<Instructor>();
        String sql = "SELECT\n"
                + "	i.instructor_id,\n"
                + "	concat_ws(\n"
                + "		' ',\n"
                + "		concat_ws(\n"
                + "			', ',\n"
                + "			i.instructor_last_name,\n"
                + "			i.instructor_first_name\n"
                + "		),\n"
                + "		CONCAT(\n"
                + "			SUBSTR(\n"
                + "				i.instructor_middle_name,\n"
                + "				1,\n"
                + "				1\n"
                + "			),\n"
                + "			'.'\n"
                + "		)\n"
                + "	) instructor_fullname\n"
                + "FROM\n"
                + "	instructors i\n"
                + "WHERE college_id  = ?\n"
                + "ORDER BY concat_ws(\n"
                + "		' ',\n"
                + "		concat_ws(\n"
                + "			', ',\n"
                + "			i.instructor_last_name,\n"
                + "			i.instructor_first_name\n"
                + "		),\n"
                + "		CONCAT(\n"
                + "			SUBSTR(\n"
                + "				i.instructor_middle_name,\n"
                + "				1,\n"
                + "				1\n"
                + "			),\n"
                + "			'.'\n"
                + "		)\n"
                + "	)";

        PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
        preparedStatement.setInt(1, collegeID);
        log.info(preparedStatement.toString());
        ResultSet rs = preparedStatement.executeQuery();
        while (rs.next()) {
            Instructor instructor = new Instructor();
            instructor.setInstructorID(rs.getInt("instructor_id"));
            instructor.setInstructorFullname(rs.getString("instructor_fullname"));
            nameList.add(instructor);
        }
        closeConnection(getConnectionObject());
        return nameList;
    }

    public static Instructor findByID(int ID) {
        PreparedStatement preparedStatementID;
        Instructor instructor = new Instructor();
        try {
            String sql = "SELECT\n"
                    + "i.instructor_idno,\n"
                    + "i.instructor_id,\n"
                    + "i.instructor_first_name,\n"
                    + "i.instructor_middle_name,\n"
                    + "i.instructor_last_name,\n"
                    + "i.instructor_visible,\n"
                    + "i.college_id,\n"
                    + "c.college_code,\n"
                    + "c.college_name\n"
                    + "FROM instructors i\n"
                    + "inner join users u\n"
                    + "on i.instructor_idno = u.user_idno\n"
                    + "inner join colleges c\n"
                    + "on i.college_id = c.college_id"
                    + "WHERE i.instructor_id=?";
            preparedStatementID = getConnectionObject().prepareStatement(sql);
            preparedStatementID.setInt(1, ID);
            log.info(preparedStatementID.toString());
            ResultSet rs = preparedStatementID.executeQuery();

            while (rs.next()) {
                instructor.setInstructorIDNo(rs.getString("instructor_idno"));
                instructor.setInstructorID(rs.getInt("instructor_id"));
                instructor.setInstructorFirstName(rs.getString("instructor_first_name"));
                instructor.setInstructorMiddleName(rs.getString("instructor_middle_name"));
                instructor.setInstructorLastName(rs.getString("instructor_last_name"));
                instructor.setCollegeCode(rs.getString("college_code"));
                instructor.setCollegeName(rs.getString("college_name"));
                instructor.setCollegeID(rs.getInt("college_id"));
                instructor.setInstructorVisible(rs.getInt("instructor_visible"));

            }
        } catch (Exception ex) {
            closeConnection(getConnectionObject());
            log.error(ex);
        }
        closeConnection(getConnectionObject());
        return instructor;
    }

    public static Instructor findByIDno(String IDNumber) {
        PreparedStatement preparedStatementID;
        Instructor instructor = new Instructor();
        try {
            String sql = "SELECT\n"
                    + "i.instructor_idno,\n"
                    + "i.instructor_id,\n"
                    + "i.instructor_first_name,\n"
                    + "i.instructor_middle_name,\n"
                    + "i.instructor_last_name,\n"
                    + "i.instructor_visible,\n"
                    + "i.college_id,\n"
                    + "c.college_code,\n"
                    + "c.college_name\n"
                    + "FROM instructors i\n"
                    + "inner join users u\n"
                    + "on i.instructor_idno = u.user_idno\n"
                    + "inner join colleges c\n"
                    + "on i.college_id = c.college_id"
                    + "WHERE i.instructor_idno=?";
            preparedStatementID = getConnectionObject().prepareStatement(sql);
            preparedStatementID.setString(1, IDNumber);
            log.info(preparedStatementID.toString());
            ResultSet rs = preparedStatementID.executeQuery();

            while (rs.next()) {
                instructor.setInstructorIDNo(rs.getString("instructor_idno"));
                instructor.setInstructorID(rs.getInt("instructor_id"));
                instructor.setInstructorFirstName(rs.getString("instructor_first_name"));
                instructor.setInstructorMiddleName(rs.getString("instructor_middle_name"));
                instructor.setInstructorLastName(rs.getString("instructor_last_name"));
                instructor.setCollegeCode(rs.getString("college_code"));
                instructor.setCollegeName(rs.getString("college_name"));
                instructor.setCollegeID(rs.getInt("college_id"));
                instructor.setInstructorVisible(rs.getInt("instructor_visible"));

            }
        } catch (Exception ex) {
            closeConnection(getConnectionObject());
            log.error(ex);
        }
        closeConnection(getConnectionObject());
        return instructor;
    }

    public static List<Instructor> getInstructors() {
        List<Instructor> instructorList = new ArrayList<Instructor>();
        PreparedStatement preparedStatementID;
        try {
            String sql = "SELECT\n"
                    + "i.instructor_idno,\n"
                    + "i.instructor_id,\n"
                    + "i.instructor_first_name,\n"
                    + "i.instructor_middle_name,\n"
                    + "i.instructor_last_name,\n"
                    + "i.instructor_visible,\n"
                    + "i.college_id,\n"
                    + "c.college_code,\n"
                    + "c.college_name\n"
                    + "FROM instructors i\n"
                    + "inner join users u\n"
                    + "on i.instructor_idno = u.user_idno\n"
                    + "inner join colleges c\n"
                    + "on i.college_id = c.college_id";
            preparedStatementID = getConnectionObject().prepareStatement(sql);
            log.info(preparedStatementID.toString());
            ResultSet rs = preparedStatementID.executeQuery();

            Instructor instructor;
            while (rs.next()) {
                instructor = new Instructor();
                instructor.setInstructorIDNo(rs.getString("instructor_idno"));
                instructor.setInstructorID(rs.getInt("instructor_id"));
                instructor.setInstructorFirstName(rs.getString("instructor_first_name"));
                instructor.setInstructorMiddleName(rs.getString("instructor_middle_name"));
                instructor.setInstructorLastName(rs.getString("instructor_last_name"));
                instructor.setCollegeCode(rs.getString("college_code"));
                instructor.setCollegeName(rs.getString("college_name"));
                instructor.setCollegeID(rs.getInt("college_id"));
                instructor.setInstructorVisible(rs.getInt("instructor_visible"));

                instructorList.add(instructor);
            }
        } catch (Exception ex) {
            closeConnection(getConnectionObject());
            log.error(ex);
        }
        closeConnection(getConnectionObject());
        return instructorList;
    }

    public static int add(Instructor instructor) throws Exception {
        try {
            int result = 0;
            String sql = "INSERT INTO instructors(instructor_idno, instructor_first_name,instructor_middle_name,instructor_last_name,"
                    + "instructor_visible, college_id) VALUES (?,?,?,?,?,?)";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setString(1, instructor.getInstructorIDNo());
            preparedStatement.setString(2, instructor.getInstructorFirstName());
            preparedStatement.setString(3, instructor.getInstructorMiddleName());
            preparedStatement.setString(4, instructor.getInstructorLastName());
            preparedStatement.setInt(5, instructor.getInstructorVisible());
            preparedStatement.setInt(6, instructor.getCollegeID());
            log.info(preparedStatement.toString());
            if (preparedStatement.executeUpdate() > 0) {
                result = setUserCredential(instructor);
                if (result <= 0) {
                    throw (new Exception("Unable to create login credential for the instructor."));
                }
            } else {
                throw (new Exception("Unable insert the instructor."));
            }
            return result;
        } catch (Exception ex) {
            log.error(ex);
            throw ex;
        }
    }

    public static int update(Instructor instructor) throws Exception {
        try {
            int result = 0;
            String sql = "UPDATE instructors SET instructor_idno =?, instructor_first_name = ?,instructor_middle_name = ?,instructor_last_name = ?,"
                    + "instructor_visible=?, college_id=? WHERE instructor_id=?";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setString(1, instructor.getInstructorIDNo());
            preparedStatement.setString(2, instructor.getInstructorFirstName());
            preparedStatement.setString(3, instructor.getInstructorMiddleName());
            preparedStatement.setString(4, instructor.getInstructorLastName());
            preparedStatement.setInt(5, instructor.getInstructorVisible());
            preparedStatement.setInt(6, instructor.getCollegeID());
            preparedStatement.setInt(7, instructor.getInstructorID());
            log.info(preparedStatement.toString());
            if (preparedStatement.executeUpdate() > 0) {
                result = updateUserCredential(instructor);
                if (result <= 0) {
                    throw (new Exception("Unable to update login credential for the instructor."));
                }
            } else {
                throw (new Exception("Unable update the instructor."));
            }
            return result;
        } catch (Exception ex) {
            log.error(ex);
            throw ex;
        }
    }

    public static int delete(int instructorID) throws Exception {
        try {
            int result = 0;
            String sql = "UPDATE instructors set instructor_visible=0 WHERE instructor_id=?";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setInt(1, instructorID);
            log.info(preparedStatement.toString());
            if (preparedStatement.executeUpdate() > 0) {
                result = disableUserCredential(instructorID);
                if (result <= 0) {
                    throw (new Exception("Unable to disable login credential for the instructor."));
                }
            } else {
                throw (new Exception("Unable deactivate the instructor."));
            }
            return result;
        } catch (Exception ex) {
            log.error(ex);
            throw ex;
        }
    }

    public static int setUserCredential(Instructor instructor) throws Exception {
        try {
            String sql = "INSERT INTO users(user_idno, user_first_name,user_middle_name,user_last_name,"
                    + "user_visible, college_id,user_password,role_id) VALUES (?,?,?,?,?,?,md5(?),1)";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setString(1, instructor.getInstructorIDNo());
            preparedStatement.setString(2, instructor.getInstructorFirstName());
            preparedStatement.setString(3, instructor.getInstructorMiddleName());
            preparedStatement.setString(4, instructor.getInstructorLastName());
            preparedStatement.setInt(5, instructor.getInstructorVisible());
            preparedStatement.setInt(6, instructor.getCollegeID());
            preparedStatement.setString(7, instructor.getInstructorIDNo());
            log.info(preparedStatement.toString());
            return preparedStatement.executeUpdate();
        } catch (Exception ex) {
            log.error(ex);
            throw ex;
        }
    }

    public static int updateUserCredential(Instructor instructor) throws Exception {
        try {
            String sql = "UPDATE users SET user_first_name = ?,user_middle_name = ?,user_last_name = ?,"
                    + "user_visible=?, college_id=? WHERE user_idno =?";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);

            preparedStatement.setString(1, instructor.getInstructorFirstName());
            preparedStatement.setString(2, instructor.getInstructorMiddleName());
            preparedStatement.setString(3, instructor.getInstructorLastName());
            preparedStatement.setInt(4, instructor.getInstructorVisible());
            preparedStatement.setInt(5, instructor.getCollegeID());
            preparedStatement.setString(6, instructor.getInstructorIDNo());
            log.info(preparedStatement.toString());
            return preparedStatement.executeUpdate();
        } catch (Exception ex) {
            log.error(ex);
            throw ex;
        }
    }

    public static int disableUserCredential(int instructorID) throws Exception {
        try {
            String sql = "UPDATE users set user_visible=0 WHERE instructor_idno=?";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setString(1, findByID(instructorID).getInstructorIDNo());
            log.info(preparedStatement.toString());
            return preparedStatement.executeUpdate();
        } catch (Exception ex) {
            log.error(ex);
            throw ex;
        }
    }
}
